const config = require("../../config");
const db = require("../db");
// const patrol = require("../methods/patrol");
// const user = require("../methods/user");
const formatDateToMySQL = require("../tools/formatDateToMySQL");
const tool = require("../tools/tool");
// const alert = require("./alert");
// const alert = require("./alert");
// const campuses = require("./campuses");
// const categories = require("./categories");
// const permissions = require("./permissions");
// const sendMessage = require('./sendMessage');
// const settings = require("./settings");

const autoDeleteStatus0Patrol = {
    data: [],
    async add(id) {
        this.data.push({
            timer: setTimeout(() => {
                this.delete(id)
            }, 1000 * 60 * 60),
            id
        })
    },
    async delete(id) {
        let re = await db.selectWithParams(`select * from patrols where id = ?`, [id])
        if (!re.length) return
        re = re[0]
        if (re.status) return
        await db.deleteById('patrols', id)
    },
    removeTimer(id) {
        this.data = this.data.filter(item => {
            if (item.id == id) clearTimeout(item.timer)
            return item.id != id
        })
    }
}

const exp = {
    async exist(patrolId) {
        return (await this.getHead(patrolId)) !== undefined
    },
    async format(item, currentLoginedUserId) {
        if (!item.detail) item.detail = await this.get.singlePatrolFullData(item.id, item.userId, currentLoginedUserId, true)
        item.status_read = (() => {
            if (item.status == 0) return '未发布'
            if (item.status == 1) return '等待'
            if (item.status == 2) return '延期'
            if (item.status == 3) return '满意度调查中'
            if (item.status == 4) return '完成'
            if (item.status == 5) return '拒绝'
            return `未知(${item.status})`
        })()
        item.endTime_read = tool.formatTimeNew(new Date(item.endTime))
        item.createdAt_read = tool.formatTimeNew(new Date(item.createdAt))
        const user1 = require('./user')
        item.userData = await user1.getSingleUserInfoByUserId(item.userId, true, true)
        item.campusInfo = await tool.methods.campuses().getInfo(item.campusId)
        item.categoryInfo = await tool.methods.categories().getInfo(item.categoryId)
        item.highLightColor = (() => {
            if (item.status == 1) return 'rgba(0,120,215,1)'
            if (item.status == 2) return '#7100ac'
            if (item.status == 3) return '#01bd40'
            return 'rgba(0,0,0,0)'
        })()
        item.highLightBgColor = (() => {
            if (item.status == 1) return 'rgba(0,120,215,0.1)'
            if (item.status == 2) return '#7100ac0f'
            if (item.status == 3) return '#01bd400f'
            return undefined
        })()
        item.imageList = [
            ...(() => {
                if (item.image1) return [item.image1]
                return []
            })(),
            ...(() => {
                if (item.image2) return [item.image2]
                return []
            })(),
            ...(() => {
                if (item.image3) return [item.image3]
                return []
            })(),
            ...(() => {
                if (item.image4) return [item.image4]
                return []
            })(),
            ...(() => {
                if (item.image5) return [item.image5]
                return []
            })(),
        ]
        item.location1 = item.location1 ? parseFloat(item.location1) : undefined
        item.location2 = item.location2 ? parseFloat(item.location2) : undefined
        item.showMap = item.location1 !== undefined
        item.isHandled = (item.status == 1 || item.status == 2) ? false : true
        item.endTime_detail = {
            num: (item.endTime1 ? 1 : 0) + (item.endTime2 ? 1 : 0) + (item.endTime3 ? 1 : 0),
            list: await (async () => {
                let re = [
                    {
                        time: item.endTime,
                    },
                    {
                        time: item.endTime1,
                    },
                    {
                        time: item.endTime2,
                    },
                    {
                        time: item.endTime3,
                    }
                ].sort((a, b) => {
                    return new Date(a.time) - new Date(b.time)
                }).filter(item => item.time)
                if (re[0]) re[0].userId = item.userId
                if (re[1]) re[1].userId = item.delay1UserId
                if (re[2]) re[2].userId = item.delay2UserId
                if (re[3]) re[3].userId = item.delay3UserId
                for (let i = 0; i < re.length - 1; i++) {
                    re[i] = {
                        from: re[i],
                        from_read: tool.formatTimeNew(re[i].time),
                        to: re[i + 1],
                        to_read: tool.formatTimeNew(re[i + 1].time),
                        size: tool.formatTimeDifference(re[i].time, re[i + 1].time),
                        userData: await (async () => {
                            const user = require('./user')
                            let re1 = await tool.methods.user().getSingleUserInfoByUserId(re[i].userId, true, true)
                            if (!re1) return '账号已注销'
                            return re1
                        })(),
                    }
                }
                return re.slice(0, re.length - 1)
            })(),
            size: await (async () => {
                let re = [
                    {
                        time: item.endTime,
                    },
                    {
                        time: item.endTime1,
                    },
                    {
                        time: item.endTime2,
                    },
                    {
                        time: item.endTime3,
                    }
                ].sort((a, b) => {
                    return new Date(a.time) - new Date(b.time)
                }).filter(item => item.time)
                return tool.formatTimeDifference(re[0].time, re[re.length - 1].time)
            })(),
            over: new Date(item.endTime) > new Date() ? true : false,
            overSize: tool.formatTimeDifference(item.endTime, new Date())
        }
        const permissions = require('./permissions')
        if (currentLoginedUserId !== undefined) {
            item.permissions = await tool.methods.permissions().getUserPatrolPermissions(currentLoginedUserId, item.id)
        } else {
            item.permissions = {}
        }
        if (!item.userData) item.userData = tool.getDeletedUserData()
        item.topTitle_hide = tool.formatDateString(item.createdAt)
        return item
    },
    async getHead(id) {
        return (await db.selectWithParams(`select * from patrols where id = ?`, [id]))[0]
    },
    async store(data) {
        data.status = 0
        let id = await db.insertInto('patrols', data)
        return id
    },
    async setImage(id, fileName) {
        let re = await this.getHead(id)
        if (!re) throw '巡查不存在'
        let index = 1
        if (re.image1) index++
        if (re.image2) index++
        if (re.image3) index++
        if (re.image4) index++
        if (re.image5) index++
        if (index <= 5) {
            let value = {}
            value[`image${index}`] = fileName
            await db.updateById('patrols', id, value)
        }
    },
    async setStatus(id, status) {
        await db.updateById('patrols', id, { status })
    },
    async publish(id) {
        autoDeleteStatus0Patrol.removeTimer(id)
        await this.setStatus(id, 1)
        await tool.wait(1.5)
    },
    get: {
        async singlePatrolFullData(patrolId, thisPatrolUserId, currentLoginedUserId = undefined, simple = false) {
            const user1 = require('./user')
            let headData = await exp.getHead(patrolId)

            let handle = await db.selectWithParams(`select * from patrols_handle where patrolId = ?`, [patrolId])
            handle = handle.sort((a, b) => {
                return new Date(a.createdAt) - new Date(b.createdAt)
            })
            handle = await tool.array.format(handle, async (item) => {
                item.tag = 'handle'
                item.userData = await user1.getSingleUserInfoByUserId(item.userId, true)
                if (!item.userData) item.userData = tool.getDeletedUserData()
                item.createdAt_read = tool.formatTimeNew(new Date(item.createdAt))
                item.isOvertime = (() => {
                    if (new Date(item.createdAt) > new Date(headData.endTime)) {
                        return true
                    }
                    return false
                })()
                if (item.isOvertime) item.overtime = tool.formatTimeDifference(item.createdAt, headData.endTime)
                item.usedTime = tool.formatTimeDifference(item.createdAt, headData.createdAt)
                item.imageList = [
                    ...(() => {
                        if (item.image1) return [item.image1]
                        return []
                    })(),
                    ...(() => {
                        if (item.image2) return [item.image2]
                        return []
                    })(),
                    ...(() => {
                        if (item.image3) return [item.image3]
                        return []
                    })(),
                    ...(() => {
                        if (item.image4) return [item.image4]
                        return []
                    })(),
                    ...(() => {
                        if (item.image5) return [item.image5]
                        return []
                    })(),
                ]
                return item
            })

            let review = await db.selectWithParams(`select * from patrols_review where patrolId = ?`, [patrolId])
            review = review.sort((a, b) => {
                return new Date(a.createdAt) - new Date(b.createdAt)
            })
            review = await tool.array.format(review, async (item) => {
                item.tag = 'review'
                item.userData = await user1.getSingleUserInfoByUserId(item.userId, true)
                if (!item.userData) item.userData = tool.getDeletedUserData()
                item.createdAt_read = tool.formatTimeNew(new Date(item.createdAt))
                return item
            })

            let feedback = await db.selectWithParams(`select * from patrols_feedback where patrolId = ?`, [patrolId])
            feedback = feedback.sort((a, b) => {
                return new Date(a.createdAt) - new Date(b.createdAt)
            })

            const thisPatrolUserData = await user1.getSingleUserInfoByUserId(thisPatrolUserId, true)
            feedback = await tool.array.format(feedback, async (item) => {
                item.tag = 'feedback'
                item.userData = thisPatrolUserData
                if (!item.userData) item.userData = tool.getDeletedUserData()
                item.createdAt_read = tool.formatTimeNew(new Date(item.createdAt))
                return item
            })

            let comment = await db.selectWithParams(`select * from patrols_comment where patrolId = ?`, [patrolId])
            comment = comment.sort((a, b) => {
                return new Date(a.createdAt) - new Date(b.createdAt)
            })
            comment = await tool.array.format(comment, async (item) => {
                item.userData = await user1.getSingleUserInfoByUserId(item.userId, true)
                if (!item.userData) item.userData = tool.getDeletedUserData()
                item.createdAt_read = tool.formatTimeNew(new Date(item.createdAt))
                return item
            })

            let like = await tool.array.format((await db.selectWithParams(`select * from patrols_like where patrolId = ?`, [patrolId])), (item) => {
                return item.userId
            })
            let liked = (like.indexOf(currentLoginedUserId) != -1)

            if (simple) {
                return {
                    num: {
                        handle: handle.length,
                        review: review.length,
                        feedback: feedback.length,
                        comment: comment.length,
                        like: like.length
                    },
                    liked,
                    finalHandle: (() => {
                        if (!handle.length) return undefined
                        return handle[handle.length - 1]
                    })(),
                    finalReview: (() => {
                        if (!review.length) return undefined
                        if (!handle.length) return undefined
                        if (new Date(review[review.length - 1].createdAt) < new Date(handle[handle.length - 1].createdAt)) return undefined
                        return review[review.length - 1]
                    })(),
                    finalFeedBack: (() => {
                        if (!feedback.length) return undefined
                        let t = [...handle, ...review, ...feedback].sort((b, a) => {
                            return new Date(b.createdAt) - new Date(a.createdAt)
                        })
                        if (t[t.length - 1].tag == 'feedback' || (t.length > 2 && t[t.length - 2].tag == 'feedback')) {
                            if (t[t.length - 1].tag == 'feedback') {
                                return t[t.length - 1]
                            } else {
                                return t[t.length - 2]
                            }
                        } else {
                            return undefined
                        }
                    })()
                }
            }

            return {
                num: {
                    handle: handle.length,
                    review: review.length,
                    feedback: feedback.length,
                    comment: comment.length,
                    like: like.length
                },
                liked,
                commentList: comment,
                finalHandle: (() => {
                    if (!handle.length) return undefined
                    return handle[handle.length - 1]
                })(),
                finalReview: (() => {
                    if (!review.length) return undefined
                    if (!handle.length) return undefined
                    if (new Date(review[review.length - 1].createdAt) < new Date(handle[handle.length - 1].createdAt)) return undefined
                    return review[review.length - 1]
                })(),
                finalFeedBack: (() => {
                    if (!feedback.length) return undefined
                    let t = [...handle, ...review, ...feedback].sort((b, a) => {
                        return new Date(b.createdAt) - new Date(a.createdAt)
                    })
                    if (t[t.length - 1].tag == 'feedback' || (t.length > 2 && t[t.length - 2].tag == 'feedback')) {
                        if (t[t.length - 1].tag == 'feedback') {
                            return t[t.length - 1]
                        } else {
                            return t[t.length - 2]
                        }
                    } else {
                        return undefined
                    }
                })(),
                progress: [...handle, ...review, ...feedback].sort((b, a) => {
                    return new Date(b.createdAt) - new Date(a.createdAt)
                })
            }
        },
        async single(id, currentLoginedUserId = undefined, simple = false) {
            let re = await exp.getHead(id)
            if (!re) throw '巡查不存在'
            re.detail = await this.singlePatrolFullData(id, re.userId, currentLoginedUserId, simple)
            return await exp.format(re, currentLoginedUserId)
        },
        async list(
            finalId = undefined,
            size = 10,
            currentLoginedUserId = undefined,
            status = undefined,
            username = '',
            phone = '',
            departmentId = undefined,
            createdAt_start = undefined,
            createdAt_end = undefined,
            endTime_start = undefined,
            endTime_end = undefined,
            id = undefined
        ) {
            let finalCreatedAt = await (async () => {
                try {
                    let re = await exp.getHead(finalId)
                    if (finalId === undefined) {
                        return undefined
                    } else {
                        return re.createdAt
                    }
                } catch (error) {
                    return undefined
                }
            })()
            let re = await db.selectWithParams(`select patrols.*, users.username as username, users.phone as phone, users.departmentId as departmentId, users.id as userId from patrols,users where patrols.userId = users.id 
                    
                ${finalCreatedAt === undefined ? `` : ` and patrols.createdAt <= ? `}
                and status ${status === undefined ? '> ?' : ' = ?'} 
                and username like ? 
                and phone like ? 
                and departmentId ${departmentId === undefined ? ' > ? ' : ' = ? '}
                ${createdAt_start === undefined ? `` : ` and createdAt >= ? `}
                ${createdAt_end === undefined ? `` : ` and createdAt <= ? `}
                ${endTime_start === undefined ? `` : ` and endTime >= ? `}
                ${endTime_end === undefined ? `` : ` and endTime <= ? `}
                ${id === undefined ? '' : ` and id = ? `}

                 order by createdAt DESC limit ?`,

                [
                    ...await (async () => {
                        if (finalCreatedAt === undefined) {
                            return []
                        } else {
                            return [finalCreatedAt]
                        }
                    })(),
                    status === undefined ? 0 : status,
                    `%${username}%`,
                    `%${phone}%`,
                    departmentId === undefined ? 0 : departmentId,
                    ...(() => {
                        if (createdAt_start) {
                            return [createdAt_start]
                        } else {
                            return []
                        }
                    })(),
                    ...(() => {
                        if (createdAt_end) {
                            return [createdAt_end]
                        } else {
                            return []
                        }
                    })(),
                    ...(() => {
                        if (endTime_start) {
                            return [endTime_start]
                        } else {
                            return []
                        }
                    })(),
                    ...(() => {
                        if (endTime_end) {
                            return [endTime_end]
                        } else {
                            return []
                        }
                    })(),
                    ...(() => {
                        if (id === undefined) return []
                        return [id]
                    })(),
                    size + 1
                ])
            re = await tool.array.format(re.sort((b, a) => {
                return new Date(a.createdAt) - new Date(b.createdAt)
            }), async (item) => {
                return await exp.format(item, currentLoginedUserId)
            })
            re = tool.addTopTitleToArr(re)
            return finalId === undefined ? re : re.slice(1)
        },
        async userPatrolNum(userId, campusId, categoryId, status) {
            let re = await db.selectWithParams(`select count(*) as total from patrols where userId = ? and campusId = ? and categoryId = ? and status = ?`, [userId, campusId, categoryId, status])
            return re[0].total
        },
        async userPreToHandlePatrolNum(userId, campusId, categoryId, isDelay = false) {
            let t = await db.selectWithParams(`select count(*) as total from patrols,permissions where permissions.userId = ? and permissions.type = 1 and patrols.campusId = permissions.campusId and patrols.categoryId = permissions.categoryId and status = ? and patrols.campusId = ? and patrols.categoryId = ? `, [userId, isDelay ? 2 : 1, campusId, categoryId])
            return t[0].total
        },
        async userPreToReviewPatrolNum(userId, campusId, categoryId) {
            let t = await db.selectWithParams(`select count(*) as total from patrols,permissions where permissions.userId = ? and permissions.type = 2 and patrols.campusId = permissions.campusId and patrols.categoryId = permissions.categoryId and status >= 3 and patrols.campusId = ? and patrols.categoryId = ? `, [userId, campusId, categoryId])
            return t[0].total
        },
        async userTasks(groupName = '', campusId = undefined, categoryId = undefined, isDelay = false, userId = undefined, finalId = undefined, size = 10) {
            if (campusId === undefined) throw '校区ID错误'
            if (categoryId === undefined) throw '门类ID错误'
            if (userId === undefined) throw '用户ID错误'
            let re = []
            if (groupName == 'preToHandle') {
                re = await db.selectWithParams(`select * from patrols where campusId = ? and categoryId = ? and status = ? ${finalId === undefined ? `` : ` and id < ? `} order by createdAt DESC limit 0,?`,
                    [
                        campusId,
                        categoryId,
                        isDelay ? 2 : 1,
                        ...(() => {
                            if (finalId === undefined) return []
                            return [finalId]
                        })(),
                        size
                    ]
                )
            }
            if (groupName == 'preToReview') {
                re = await db.selectWithParams(`select * from patrols where campusId = ? and categoryId = ? and status >= 3 ${finalId === undefined ? `` : ` and id < ? `} order by createdAt DESC limit 0,?`,
                    [
                        campusId,
                        categoryId,
                        ...(() => {
                            if (finalId === undefined) return []
                            return [finalId]
                        })(),
                        size
                    ]
                )
            }
            if (groupName == 'waitingForHandle') {
                re = await db.selectWithParams(`select * from patrols where userId = ? and campusId = ? and categoryId = ? and status = ? ${finalId === undefined ? `` : ` and id < ? `} order by createdAt DESC limit 0,?`,
                    [
                        userId,
                        campusId,
                        categoryId,
                        isDelay ? 2 : 1,
                        ...(() => {
                            if (finalId === undefined) return []
                            return [finalId]
                        })(),
                        size
                    ]
                )
            }
            if (groupName == 'waitingForRating') {
                re = await db.selectWithParams(`select * from patrols where userId = ? and campusId = ? and categoryId = ? and status = 3 ${finalId === undefined ? `` : ` and id < ? `} order by createdAt DESC limit 0,?`,
                    [
                        userId,
                        campusId,
                        categoryId,
                        ...(() => {
                            if (finalId === undefined) return []
                            return [finalId]
                        })(),
                        size
                    ]
                )
            }
            if (groupName == 'myHistory') {
                re = await db.selectWithParams(`select * from patrols where userId = ? ${finalId === undefined ? `` : ` and id < ? `} order by createdAt DESC limit 0,?`,
                    [
                        userId,
                        ...(() => {
                            if (finalId === undefined) return []
                            return [finalId]
                        })(),
                        size
                    ]
                )
            }
            re = await tool.array.format(re, async (item) => {
                return await exp.format(item, userId)
            })
            re = tool.addTopTitleToArr(re)
            return re
        }
    },
    async delete(id) {
        const handleDelete = async (tableName, arr) => {
            await tool.array.format(arr, async (item) => {
                if (item.image1) await tool.deleteFile(item.image1)
                if (item.image2) await tool.deleteFile(item.image2)
                if (item.image3) await tool.deleteFile(item.image3)
                if (item.image4) await tool.deleteFile(item.image4)
                if (item.image5) await tool.deleteFile(item.image5)
                await db.exe(`delete from ${tableName} where id = ${item.id}`)
            })
        }
        const tables = ['patrols_like', 'patrols_feedback', 'patrols_comment', 'patrols_handle', 'patrols_review']
        await tool.array.format(tables, async (item) => {
            // console.log(await db.selectWithParams(`select * from ${item} where patrolId = ?`, [id]))
            await handleDelete(item, await db.selectWithParams(`select * from ${item} where patrolId = ?`, [id]))
        })
        let item = await this.format(await this.getHead(id))
        if (item.image1) await tool.deleteFile(item.image1)
        if (item.image2) await tool.deleteFile(item.image2)
        if (item.image3) await tool.deleteFile(item.image3)
        if (item.image4) await tool.deleteFile(item.image4)
        if (item.image5) await tool.deleteFile(item.image5)
        await db.deleteById('patrols', id)
    },
    async update(patrolId, values = {}) {
        await db.updateById('patrols', patrolId, values)
    },
    async delay(patrolId, userId, datetime) {
        const user = require('./user')
        const permissions = require('./permissions')
        let patrolInfo = await this.format(await this.getHead(patrolId), userId)
        if (!patrolInfo) throw '巡查不存在'
        let userData = await tool.methods.user().getSingleUserInfoByUserId(userId)
        if (!userData) throw '用户不存在'
        if (!(await tool.methods.permissions().checkUserPermission(userId, 1, patrolInfo.campusId, patrolInfo.categoryId))) {
            throw '权限不够'
        }
        if (patrolInfo.status == 1 || patrolInfo.status == 2) {
            if (patrolInfo.endTime1 && patrolInfo.endTime2 && patrolInfo.endTime3) {
                throw '已延期三次，不能再延期'
            } else {
                if (new Date(patrolInfo.endTime) >= new Date(datetime)) {
                    throw '新的截止时间必须晚于当前截止时间'
                } else {
                    let index = 1
                    if (patrolInfo.endTime1) index++
                    if (patrolInfo.endTime2) index++
                    if (patrolInfo.endTime3) index++
                    if (index > 3) {
                        throw '已延期三次，不能再延期'
                    } else {
                        if (Math.abs(new Date(datetime) - new Date(patrolInfo.endTime)) > 1000 * 60 * 60 * 24 * parseInt(await tool.methods.settings().getItem('maxDelayTime'))) throw `延期时间不能超过${await tool.methods.settings().getItem('maxDelayTime')}天`
                        if (patrolInfo.detail.num.handle) throw '被驳回的巡查不能申请延期'
                        let value = {
                            status: 2
                        }
                        value[`endTime${index}`] = patrolInfo.endTime
                        value[`delay${index}UserId`] = userId
                        value.endTime = formatDateToMySQL(new Date(datetime))
                        await this.update(patrolId, value)
                        //发送短信
                        //                         await tool.methods.sendMessage().toPatrolCreater(patrolId, await tool.methods.sendMessage().getPatrolInMessageFormat(patrolId, (infoStr, item) => {
                        //                             return `巡查人您好，您提交的巡查【#${item.id}】的截止时间已被相关负责人【${userData.username} 📞${userData.phone}】推迟至${tool.formatTimeNew(new Date(datetime))}。
                        // ————————————————
                        // ${infoStr}`
                        //                         }))
                        //                         await tool.methods.sendMessage().toHandleGroup(patrolInfo.campusId, patrolInfo.categoryId, await tool.methods.sendMessage().getPatrolInMessageFormat(patrolId, (infoStr, item) => {
                        //                             return `负责人您好，巡查【#${item.id}】的截止时间已被【${userData.username} 📞${userData.phone}】推迟至${tool.formatTimeNew(new Date(datetime))}。
                        // ————————————————
                        // ${infoStr}`
                        //                         }))
                    }
                }
            }
        } else {
            throw '当前巡查未处于可推迟的状态'
        }
    },
    async handle(patrolId, userId, values) {
        let patrolInfo = await this.getHead(patrolId)
        let userData = await require('./user').getSingleUserInfoByUserId(userId)
        values = Object.assign(values, { userId })
        if (!patrolInfo) throw '巡查不存在'
        if (!userData) throw '用户不存在'
        let permissions = await require('./permissions').getUserPatrolPermissions(userId, patrolId)
        if (!(permissions).handle) throw '无权处理'
        await db.insertInto('patrols_handle', values)
        if (values.reject) {
            // 拒绝处理
            await this.setStatus(patrolId, 5)
        } else {
            if (await tool.methods.settings().getItem('progress_feedBack')) {
                // 满意度调查
                await this.setStatus(patrolId, 3)
            } else {
                // 完成
                await this.setStatus(patrolId, 4)
            }
        }

    },
    async review(patrolId, userId, desc) {
        let patrolInfo = await this.getHead(patrolId)
        let userData = await require('./user').getSingleUserInfoByUserId(userId)
        if (!patrolInfo) throw '巡查不存在'
        if (!userData) throw '用户不存在'
        let permissions = await require('./permissions').getUserPatrolPermissions(userId, patrolId)
        if (!(permissions).review) throw '无权驳回'
        if (!desc) throw '描述为空'
        await db.insertInto('patrols_review', {
            patrolId,
            userId,
            desc
        })
        await this.setStatus(patrolId, 1)
    },
    async feedBack(patrolId, userId, rating, desc, f = false) {
        let patrolInfo = await this.getHead(patrolId)
        let userData = await require('./user').getSingleUserInfoByUserId(userId)
        if (!patrolInfo) throw '巡查不存在'
        if (!userData) throw '用户不存在'
        let permissions = await require('./permissions').getUserPatrolPermissions(userId, patrolId)
        if (!(permissions).feedBack) throw '无权评价'
        if (!rating) throw '未选择评分'
        if (desc.indexOf('(系统自动)') + 1 && !f) throw '换一个文字描述'
        await db.insertInto('patrols_feedback', {
            patrolId,
            rating,
            desc
        })
        await this.setStatus(patrolId, 4)
    },
    async priority(patrolId, userId) {
        let patrolInfo = await this.get.single(patrolId, userId)
        if (!patrolInfo.permissions.priority) throw '无权操作'
        if (patrolInfo.priorityTime == '' || tool.formatDateString(patrolInfo.priorityTime) != tool.formatDateString(new Date())) {
            await this.update(patrolId, {
                priorityTime: formatDateToMySQL(new Date())
            })
        } else {
            throw '今天已经催办过'
        }
    },
    async search(form, withTotal = false) {
        // 假设请求参数通过 form 传入
        const conditions = {
            id: form.id,                               // 巡查id（精准匹配）
            createdStart: form.createdStart,           // 创建时间开始
            createdEnd: form.createdEnd,               // 创建时间结束
            patrolDesc: form.patrolDesc,               // 描述（模糊查询）
            imageCount: form.imageCount,               // 图片个数（统计 image1~image5 非空数）
            hasLocation: form.hasLocation,             // 有无位置（true/false 或 1/0）
            campusId: form.campusId,                   // 校区Id
            categoryId: form.categoryId,               // 类别id
            status: form.status,                       // 状态
            endTimeStart: form.endTimeStart,           // 结束时间开始（以 p.endTime 为准）
            endTimeEnd: form.endTimeEnd,               // 结束时间结束
            delayCount: form.delayCount,               // 延期次数（统计 delay1UserId～delay3UserId 非空数）
            delayUserId: form.delayUserId,             // 延期人（任一字段匹配）

            // feedback 评价条件（patrols_feedback 表）
            feedbackCreatedStart: form.feedbackCreatedStart,
            feedbackCreatedEnd: form.feedbackCreatedEnd,
            feedbackDesc: form.feedbackDesc,
            feedbackRating: form.feedbackRating,
            feedbackCount: form.feedbackCount,         // 评价次数（HAVING过滤）

            // 处理条件（patrols_handle 表）
            handleCreatedStart: form.handleCreatedStart,
            handleCreatedEnd: form.handleCreatedEnd,
            handleDesc: form.handleDesc,
            handleImageCount: form.handleImageCount,   // 处理图片数量（统计 handle.image1~image5 非空数）
            handleCount: form.handleCount,             // 处理次数（HAVING过滤）
            handleReject: form.handleReject,           // 是否拒绝处理（如传1表示只选拒绝记录）

            // 驳回条件（基于 patrols_handle.reject = 1）
            rejectTimeStart: form.rejectTimeStart,
            rejectTimeEnd: form.rejectTimeEnd,
            rejectDesc: form.rejectDesc,
            rejectCount: form.rejectCount,             // 驳回次数（HAVING过滤）

            // 巡查人条件（patrols.userId 对应的 users 表，模糊查询或精准匹配）
            patrolUsername: form.patrolUsername,
            patrolPhone: form.patrolPhone,
            patrolAccount: form.patrolAccount,
            patrolDepartmentId: form.patrolDepartmentId,
            patrolRole: form.patrolRole,

            // 负责人条件（patrols_handle.userId 对应的 users 表，多个处理人只要有一人符合即可）
            handlerUsername: form.handlerUsername,
            handlerPhone: form.handlerPhone,
            handlerAccount: form.handlerAccount,
            handlerDepartmentId: form.handlerDepartmentId,
            handlerRole: form.handlerRole,

            // 审核人条件（patrols_review.userId 对应的 users 表）
            reviewerUsername: form.reviewerUsername,
            reviewerPhone: form.reviewerPhone,
            reviewerAccount: form.reviewerAccount,
            reviewerDepartmentId: form.reviewerDepartmentId,
            reviewerRole: form.reviewerRole,

            // 校区和类别名称（通过关联 campuses 和 categories 表）
            campusName: form.campusName,
            categoryName: form.categoryName
        };

        // 分页参数：finalId 和 size
        // 当 finalId 未传（或为null/undefined/空字符串）时默认取出前 size 条数据
        const finalId = form.finalId;
        const size = parseInt(form.size, 10) || 10;

        let params = [];
        let sql = `
    SELECT 
      p.*
    FROM patrols p
    LEFT JOIN patrols_feedback pf ON pf.patrolId = p.id
    LEFT JOIN patrols_handle ph ON ph.patrolId = p.id
    LEFT JOIN patrols_review pr ON pr.patrolId = p.id
    -- 巡查人信息
    LEFT JOIN users pu ON pu.id = p.userId
    -- 处理人信息（一个巡查可能有多条处理记录）
    LEFT JOIN users hu ON hu.id = ph.userId
    -- 审核人信息
    LEFT JOIN users ru ON ru.id = pr.userId
    LEFT JOIN campuses c ON c.id = p.campusId
    LEFT JOIN categories cat ON cat.id = p.categoryId
    WHERE 1=1
  `;

        // ───────── 基础条件 ─────────
        // 巡查id
        if (conditions.id) {
            sql += " AND p.id = ? ";
            params.push(conditions.id);
        }
        // 创建时间范围
        if (conditions.createdStart) {
            sql += " AND p.createdAt >= ? ";
            params.push(conditions.createdStart);
        }
        if (conditions.createdEnd) {
            sql += " AND p.createdAt <= ? ";
            params.push(conditions.createdEnd);
        }
        // 描述（模糊查询）
        if (conditions.patrolDesc) {
            sql += " AND p.desc LIKE ? ";
            params.push(`%${conditions.patrolDesc}%`);
        }
        // 图片个数（统计 image1~image5 非空的数值必须精确匹配）
        if (conditions.imageCount !== undefined && conditions.imageCount !== '') {
            sql += ` AND (
      (CASE WHEN p.image1 IS NOT NULL AND p.image1 <> '' THEN 1 ELSE 0 END) +
      (CASE WHEN p.image2 IS NOT NULL AND p.image2 <> '' THEN 1 ELSE 0 END) +
      (CASE WHEN p.image3 IS NOT NULL AND p.image3 <> '' THEN 1 ELSE 0 END) +
      (CASE WHEN p.image4 IS NOT NULL AND p.image4 <> '' THEN 1 ELSE 0 END) +
      (CASE WHEN p.image5 IS NOT NULL AND p.image5 <> '' THEN 1 ELSE 0 END)
    ) = ? `;
            params.push(conditions.imageCount);
        }
        // 有无位置：只要 location1 或 location2 有数据即可
        if (conditions.hasLocation !== undefined && conditions.hasLocation !== '') {
            if (conditions.hasLocation == 'true' || conditions.hasLocation == 1) {
                sql += " AND ((p.location1 IS NOT NULL AND p.location1 <> '') OR (p.location2 IS NOT NULL AND p.location2 <> '')) ";
            } else {
                sql += " AND ((p.location1 IS NULL OR p.location1 = '') AND (p.location2 IS NULL OR p.location2 = '')) ";
            }
        }
        // 校区id、类别id、状态
        if (conditions.campusId) {
            sql += " AND p.campusId = ? ";
            params.push(conditions.campusId);
        }
        if (conditions.categoryId) {
            sql += " AND p.categoryId = ? ";
            params.push(conditions.categoryId);
        }
        if (conditions.status) {
            sql += " AND p.status = ? ";
            params.push(conditions.status);
        }
        // 结束时间范围
        if (conditions.endTimeStart) {
            sql += " AND p.endTime >= ? ";
            params.push(conditions.endTimeStart);
        }
        if (conditions.endTimeEnd) {
            sql += " AND p.endTime <= ? ";
            params.push(conditions.endTimeEnd);
        }
        // 延期次数（统计 delay1UserId～delay3UserId 非空数）
        if (conditions.delayCount !== undefined && conditions.delayCount !== '') {
            sql += ` AND (
      (CASE WHEN p.delay1UserId IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN p.delay2UserId IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN p.delay3UserId IS NOT NULL THEN 1 ELSE 0 END)
    ) = ? `;
            params.push(conditions.delayCount);
        }
        // 延期人：只要任一延期字段匹配即可
        if (conditions.delayUserId) {
            sql += " AND (p.delay1UserId = ? OR p.delay2UserId = ? OR p.delay3UserId = ?) ";
            params.push(conditions.delayUserId, conditions.delayUserId, conditions.delayUserId);
        }

        // ───────── 评价条件（patrols_feedback 表） ─────────
        if (conditions.feedbackCreatedStart) {
            sql += " AND pf.createdAt >= ? ";
            params.push(conditions.feedbackCreatedStart);
        }
        if (conditions.feedbackCreatedEnd) {
            sql += " AND pf.createdAt <= ? ";
            params.push(conditions.feedbackCreatedEnd);
        }
        if (conditions.feedbackDesc) {
            sql += " AND pf.desc LIKE ? ";
            params.push(`%${conditions.feedbackDesc}%`);
        }
        if (conditions.feedbackRating) {
            sql += " AND pf.rating = ? ";
            params.push(conditions.feedbackRating);
        }
        // 注意：评价次数（feedbackCount）将在 HAVING 中处理

        // ───────── 处理条件（patrols_handle 表） ─────────
        if (conditions.handleCreatedStart) {
            sql += " AND ph.createdAt >= ? ";
            params.push(conditions.handleCreatedStart);
        }
        if (conditions.handleCreatedEnd) {
            sql += " AND ph.createdAt <= ? ";
            params.push(conditions.handleCreatedEnd);
        }
        if (conditions.handleDesc) {
            sql += " AND ph.desc LIKE ? ";
            params.push(`%${conditions.handleDesc}%`);
        }
        if (conditions.handleImageCount !== undefined && conditions.handleImageCount !== '') {
            sql += ` AND (
      (CASE WHEN ph.image1 IS NOT NULL AND ph.image1 <> '' THEN 1 ELSE 0 END) +
      (CASE WHEN ph.image2 IS NOT NULL AND ph.image2 <> '' THEN 1 ELSE 0 END) +
      (CASE WHEN ph.image3 IS NOT NULL AND ph.image3 <> '' THEN 1 ELSE 0 END) +
      (CASE WHEN ph.image4 IS NOT NULL AND ph.image4 <> '' THEN 1 ELSE 0 END) +
      (CASE WHEN ph.image5 IS NOT NULL AND ph.image5 <> '' THEN 1 ELSE 0 END)
    ) = ? `;
            params.push(conditions.handleImageCount);
        }
        if (conditions.handleReject !== undefined && conditions.handleReject !== '') {
            sql += " AND ph.reject = ? ";
            params.push(conditions.handleReject);
        }

        // ───────── 驳回条件（同属 patrols_handle 中 reject=1 的记录） ─────────
        if (conditions.rejectTimeStart) {
            sql += " AND ph.reject = 1 AND ph.updatedAt >= ? ";
            params.push(conditions.rejectTimeStart);
        }
        if (conditions.rejectTimeEnd) {
            sql += " AND ph.reject = 1 AND ph.updatedAt <= ? ";
            params.push(conditions.rejectTimeEnd);
        }
        if (conditions.rejectDesc) {
            sql += " AND ph.reject = 1 AND ph.desc LIKE ? ";
            params.push(`%${conditions.rejectDesc}%`);
        }

        // ───────── 巡查人条件（关联 users 表 pu） ─────────
        if (conditions.patrolUsername) {
            sql += " AND pu.username LIKE ? ";
            params.push(`%${conditions.patrolUsername}%`);
        }
        if (conditions.patrolPhone) {
            sql += " AND pu.phone = ? ";
            params.push(conditions.patrolPhone);
        }
        if (conditions.patrolAccount) {
            sql += " AND pu.account = ? ";
            params.push(conditions.patrolAccount);
        }
        if (conditions.patrolDepartmentId) {
            sql += " AND pu.departmentId = ? ";
            params.push(conditions.patrolDepartmentId);
        }
        if (conditions.patrolRole) {
            sql += " AND pu.role = ? ";
            params.push(conditions.patrolRole);
        }

        // ───────── 负责人条件（关联 users 表 hu） ─────────
        if (conditions.handlerUsername) {
            sql += " AND hu.username LIKE ? ";
            params.push(`%${conditions.handlerUsername}%`);
        }
        if (conditions.handlerPhone) {
            sql += " AND hu.phone = ? ";
            params.push(conditions.handlerPhone);
        }
        if (conditions.handlerAccount) {
            sql += " AND hu.account = ? ";
            params.push(conditions.handlerAccount);
        }
        if (conditions.handlerDepartmentId) {
            sql += " AND hu.departmentId = ? ";
            params.push(conditions.handlerDepartmentId);
        }
        if (conditions.handlerRole) {
            sql += " AND hu.role = ? ";
            params.push(conditions.handlerRole);
        }

        // ───────── 审核人条件（关联 users 表 ru） ─────────
        if (conditions.reviewerUsername) {
            sql += " AND ru.username LIKE ? ";
            params.push(`%${conditions.reviewerUsername}%`);
        }
        if (conditions.reviewerPhone) {
            sql += " AND ru.phone = ? ";
            params.push(conditions.reviewerPhone);
        }
        if (conditions.reviewerAccount) {
            sql += " AND ru.account = ? ";
            params.push(conditions.reviewerAccount);
        }
        if (conditions.reviewerDepartmentId) {
            sql += " AND ru.departmentId = ? ";
            params.push(conditions.reviewerDepartmentId);
        }
        if (conditions.reviewerRole) {
            sql += " AND ru.role = ? ";
            params.push(conditions.reviewerRole);
        }

        // ───────── 校区和类别名称条件（关联 campuses 和 categories 表） ─────────
        if (conditions.campusName) {
            sql += " AND c.name LIKE ? ";
            params.push(`%${conditions.campusName}%`);
        }
        if (conditions.categoryName) {
            sql += " AND cat.name LIKE ? ";
            params.push(`%${conditions.categoryName}%`);
        }

        // ───────── 分页条件 ─────────
        // 如果传入 finalId，则只取 p.id 小于 finalId 的记录（注意：这要求业务上 id 与 createdAt 排序相符）
        if (finalId !== undefined && finalId !== null && finalId !== '') {
            sql += " AND p.id <= ? ";
            params.push(finalId);
        }

        // 为避免由于 LEFT JOIN 导致重复数据，按主键分组
        sql += " GROUP BY p.id ";

        // 如果有需要对聚合值（评价次数、处理次数、驳回次数）过滤，则在 HAVING 子句中追加过滤条件：
        let havingArr = [];
        if (conditions.feedbackCount !== undefined && conditions.feedbackCount !== '') {
            havingArr.push("COUNT(DISTINCT pf.id) = ?");
            params.push(conditions.feedbackCount);
        }
        if (conditions.handleCount !== undefined && conditions.handleCount !== '') {
            havingArr.push("COUNT(DISTINCT ph.id) = ?");
            params.push(conditions.handleCount);
        }
        if (conditions.rejectCount !== undefined && conditions.rejectCount !== '') {
            havingArr.push("SUM(CASE WHEN ph.reject = 1 THEN 1 ELSE 0 END) = ?");
            params.push(conditions.rejectCount);
        }
        if (havingArr.length > 0) {
            sql += " HAVING " + havingArr.join(" AND ");
        }

        let sql_c = sql

        // ───────── 排序和限制结果 ─────────
        // 按 patrols.createdAt 降序排序
        sql += " ORDER BY p.createdAt DESC ";
        // 限制返回 size 条记录
        sql += " LIMIT ? ";
        params.push(size + 1);

        // 最后调用你写的执行方法
        let re = await db.exeWithParams(sql, params)
        re = await tool.addTopTitleToArr(re)
        if (finalId !== undefined && finalId !== null && finalId !== '') {
            re = re.slice(1)
        }
        re = await tool.array.format(re, async (item) => {
            return await exp.format(item)
        })
        // execSQLWithParams(sql, params)
        //     .then(results => {
        //         // 处理查询结果
        //         res.json(results);
        //     })
        //     .catch(error => {
        //         // 处理错误
        //         res.status(500).json({ error: error.message });
        //     });
        if (withTotal) return {
            list: re,
            total: (await db.exeWithParams(`select count(*) as total from (${sql_c}) as ft`, params))[0].total
        }
        return re
    },
    async deleteAllNotHandle() {
        let re = await db.selectWithParams(`select id from patrols where status <= 2`)
        await tool.array.format(re, async (item) => {
            try {
                await this.delete(item.id)
            } catch (error) {

            }
        })
    },
    async handleSetFeedBackNow() {
        try {
            let re = await db.selectWithParams(`select id, userId from patrols where status = 3`)
            await tool.array.format(re, async (item) => {
                let data = await this.get.single(item.id, undefined, true)
                if (new Date(new Date(data.detail.finalHandle.createdAt) + 1000 * 60 * 60 * 24 * 3) < new Date()) {
                    await this.feedBack(item.id, item.userId, 1, '(系统自动)', true)
                }
            })
        } catch (error) {
            console.log(error)
        }

    },
    autoSetFeedBack() {
        // this.handleSetFeedBackNow()
        // setInterval(async () => {
        //     this.handleSetFeedBackNow()
        // }, 1000 * 60 * 5);
    },
    async exportToExcel(form, userId) {
        let data = await this.search(form)
        for (let i = 0; i < data.length; i++) {
            let c = data[i]
            data[i] = {
                id: data[i].id,
                createdAt: tool.formatDate3(new Date(data[i].createdAt)),
                username: c.userData.username,
                phone: c.userData.phone,
                account: c.userData.account,
                role: c.userData.role,
                departmentName: c.userData.departmentInfo.name,
                desc: c.desc,
                image1: c.image1 ? (config.serverURL + '/file/download/' + c.image1) : '',
                image2: c.image2 ? (config.serverURL + '/file/download/' + c.image2) : '',
                image3: c.image3 ? (config.serverURL + '/file/download/' + c.image3) : '',
                image4: c.image4 ? (config.serverURL + '/file/download/' + c.image4) : '',
                image5: c.image5 ? (config.serverURL + '/file/download/' + c.image5) : '',
                location: c.showMap ? (`position=${c.location2},${c.location1}`) : (undefined),
                campus: c.campusInfo.name,
                category: c.categoryInfo.name,
                status: c.status_read,
                endTime: tool.formatDate3(new Date(data[i].endTime)),
                delayCount: c.endTime_detail.num,
                delaySizeTotal: c.endTime_detail.size,
                ...(() => {
                    let re = {}
                    for (let i = 0; i < c.endTime_detail.list.length; i++) {
                        re[`delay${i + 1}Info`] = `${c.endTime_detail.list[i].userData.username}【${c.endTime_detail.list[i].userData.phone}】从${c.endTime_detail.list[i].from_read}调整至${c.endTime_detail.list[i].to_read}，时长${c.endTime_detail.list[i].size}`
                    }
                    return re
                })(),
                ...(() => {
                    if (c.detail.finalHandle) {
                        return {
                            finalHandle_username: c.detail.finalHandle.userData.username,
                            finalHandle_phone: c.detail.finalHandle.userData.phone,
                            finalHandle_account: c.detail.finalHandle.userData.account,
                            finalHandle_role: c.detail.finalHandle.userData.role,
                            finalHandle_departmentName: c.detail.finalHandle.userData.departmentInfo.name,
                            finalHandle_createdAt: tool.formatDate3(new Date(c.detail.finalHandle.createdAt)),
                            finalHandle_desc: c.detail.finalHandle.desc,
                            finalHandle_reject: c.detail.finalHandle.reject,
                            finalHandle_image1: c.detail.finalHandle.image1 ? (config.serverURL + '/file/download/' + c.detail.finalHandle.image1) : '',
                            finalHandle_image2: c.detail.finalHandle.image2 ? (config.serverURL + '/file/download/' + c.detail.finalHandle.image2) : '',
                            finalHandle_image3: c.detail.finalHandle.image3 ? (config.serverURL + '/file/download/' + c.detail.finalHandle.image3) : '',
                            finalHandle_image4: c.detail.finalHandle.image4 ? (config.serverURL + '/file/download/' + c.detail.finalHandle.image4) : '',
                            finalHandle_image5: c.detail.finalHandle.image5 ? (config.serverURL + '/file/download/' + c.detail.finalHandle.image5) : '',
                        }
                    } else {
                        return {}
                    }
                })(),
                ...(() => {
                    if (c.detail.finalReview) {
                        return {
                            finalReview_username: c.detail.finalReview.userData.username,
                            finalReview_phone: c.detail.finalReview.userData.phone,
                            finalReview_account: c.detail.finalReview.userData.account,
                            finalReview_role: c.detail.finalReview.userData.role,
                            finalReview_departmentName: c.detail.finalReview.userData.departmentInfo.name,
                            finalReview_createdAt: tool.formatDate3(new Date(c.detail.finalReview.createdAt)),
                            finalReview_desc: c.detail.finalReview.desc,
                        }
                    } else {
                        return {}
                    }
                })(),
                ...(() => {
                    if (c.detail.finalFeedBack) {
                        return {
                            finalFeedBack_username: c.detail.finalFeedBack.userData.username,
                            finalFeedBack_phone: c.detail.finalFeedBack.userData.phone,
                            finalFeedBack_account: c.detail.finalFeedBack.userData.account,
                            finalFeedBack_role: c.detail.finalFeedBack.userData.role,
                            finalFeedBack_departmentName: c.detail.finalFeedBack.userData.departmentInfo.name,
                            finalFeedBack_createdAt: tool.formatDate3(new Date(c.detail.finalFeedBack.createdAt)),
                            finalFeedBack_desc: c.detail.finalFeedBack.desc,
                            finalFeedBack_rating: (() => {
                                if (c.detail.finalFeedBack.rating == 1) return '非常满意'
                                if (c.detail.finalFeedBack.rating == 2) return '满意'
                                if (c.detail.finalFeedBack.rating == 3) return '一般'
                                if (c.detail.finalFeedBack.rating == 4) return '不满意'
                                if (c.detail.finalFeedBack.rating == 5) return '非常不满意'
                            })(),
                        }
                    } else {
                        return {}
                    }
                })(),
                num_handle: c.detail.num.handle,
                num_review: c.detail.num.review,
                num_feedback: c.detail.num.feedback,
            }
        }
        let fm = tool.getExportToExcelFields()
        let workbook = await tool.exportDataToExcel(data, fm, async (fileName) => {
            if (!fileName) return undefined
            if (fileName.indexOf('position') + 1) {
                return await tool.fetchImageBuffer(await tool.generateQRCode(`https://uri.amap.com/marker?${fileName}`))
            } else {
                return await tool.readImage(fileName)
            }
        })
        let fileName = await tool.saveWorkbookToFile(workbook)
        await tool.methods.alert().genExcel(userId, fileName)
    },
    async total() {
        return (await tool.dbf().select(`select count(*) as total from patrols`))[0].total
    },
    edit: {
        async campusCatrgory(patrolId, userId, campusId, categoryId) {
            let re = await exp.get.single(patrolId, userId, false)
            if (!(await tool.methods.permissions().checkUserPermission(userId, 1, re.campusId, re.categoryId))) throw '无权处理'
            await exp.update(patrolId, {
                campusId, categoryId
            })
        }
    }
}

module.exports = exp